﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using OfficeOpenXml;    
using Sunny.UI;
using EX = OfficeOpenXml.LicenseContext;
namespace ALL_Tool
{
    public partial class DataExport : UIPage
    {
        public DataExport()
        {
            InitializeComponent();
            //初始化EPPlus   非商业授权
            ExcelPackage.LicenseContext = EX.NonCommercial; 
        }      
        private void FPage2_Load(object sender, EventArgs e)
        {

        }

        private void FPage2_Initialize(object sender, EventArgs e)
        {

        }

        private void uiComboDataGridView1_Click(object sender, EventArgs e)
        {

        }
        private void uiDataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
        }
        private void uiDataGridView2_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
        }
        //类别查询 
        private void clsno_Click(object sender, EventArgs e)
        {
           string connectionString = PublicMethod.Sql_Link_Str();
            // 查询语句
            string query = "SELECT item_clsno as 类别编码, item_clsname as 类别名称 FROM t_bd_item_cls";
            
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds, "类别数据");
                            uiDataGridView2.DataSource = ds.Tables["类别数据"];
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询失败: " + ex.Message);
                }
            }

        }
        // 数据导出
        private void btnExport_Click(object sender, EventArgs e)
        {
            if (uiDataGridView2.DataSource == null)
            {
                MessageBox.Show("没有导出数据");
                return;
            }

            using (SaveFileDialog saveFileDialog = new SaveFileDialog())
            {
                saveFileDialog.Filter = "Excel Files|*.xlsx";
                saveFileDialog.Title = "Save an Excel File";

                if (saveFileDialog.ShowDialog() == DialogResult.OK)
                {
                    try
                    {
                        using (ExcelPackage excelPackage = new ExcelPackage())
                        {
                            ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1");
                            DataTable dataTable = (DataTable)uiDataGridView2.DataSource;

                            for (int i = 0; i < dataTable.Columns.Count; i++)
                            {
                                worksheet.Cells[1, i + 1].Value = dataTable.Columns[i].ColumnName;
                            }

                            for (int i = 0; i < dataTable.Rows.Count; i++)
                            {
                                for (int j = 0; j < dataTable.Columns.Count; j++)
                                {
                                    worksheet.Cells[i + 2, j + 1].Value = dataTable.Rows[i][j].ToString();
                                }
                            }

                            FileInfo fileInfo = new FileInfo(saveFileDialog.FileName);
                            excelPackage.SaveAs(fileInfo);
                        }

                        MessageBox.Show("数据导出成功！");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show($"Error: {ex.Message}");
                    }
                }
            }
        }
      
        private void ExportToExcelEPPlus(DataGridView dgv, string filePath)
        {
            
            // 使用EPPlus创建Excel包
            using (ExcelPackage package = new ExcelPackage())
            {
                // 添加工作表
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("数据信息");

                // 写入表头
                for (int i = 0; i < dgv.Columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = dgv.Columns[i].HeaderText;
                }

                // 写入数据
                for (int i = 0; i < dgv.Rows.Count; i++)
                {
                    if (dgv.Rows[i].IsNewRow) continue; // 跳过DataGridView中的新行
                    for (int j = 0; j < dgv.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1].Value = dgv.Rows[i].Cells[j].Value.ToString();
                    }
                }

                // 保存Excel文件
                using (var fileStream = new System.IO.FileStream(filePath, System.IO.FileMode.Create, System.IO.FileAccess.Write))
                {
                    package.Save();
                }

                MessageBox.Show("数据导出成功！");
            }
        }
        //供应商查询
        private void supcust_Click(object sender, EventArgs e)
        {
            string connectionString = PublicMethod.Sql_Link_Str();
            // 查询语句
            string query = "SELECT \r\nsupcust_no as 编码,\r\nsup_name as 名称,\r\nsale_way as 经营方式,\r\nregion_no as 区域,\r\nsup_man as 联系人,\r\nsup_addr as 地址,\r\nsup_tel as 电话,\r\nmobile  as 手机\r\n  FROM t_bd_supcust_info";

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds, "供应商");
                            uiDataGridView2.DataSource = ds.Tables["供应商"];
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询失败: " + ex.Message);
                }
            }
        }
        //会员查询
        private void mumber_Click(object sender, EventArgs e)
        {
            string connectionString = PublicMethod.Sql_Link_Str();
            // 查询语句
            string query = "select  card_type as 卡类编号,\r\n(select top 1 type_name from t_rm_vip_type where t_rm_vip_type.type_id = t_rm_vip_info.card_type) as 会员类别,\r\ncard_id as 会员ID,\r\ncard_flowno as 会员卡号,\r\nvip_name as 姓名,\r\nacc_num as '累计积分',\r\ndec_num as '已用积分',\r\n(acc_num-dec_num) as '剩余积分',\r\ndbo.f_get_descry(t_rm_vip_info.residual_amt) as '余额',\r\nconvert(char(10),\r\nvip_start_date,121) as 积分开始日期,\r\nconvert(char(10),vip_end_date,121) as 积分结束日期,\r\n(case card_status when '0' then '正常' when '1' then '无效' when '2' then '挂失' when '3' then '作废' when '4' then '黑名单' when '5' then '过期' end) as 卡状态,\r\nvip_sex as 性别,\r\nconvert(char(10),\r\nbirthday,121) as 生日,\r\nvip_tel as 电话,\r\nmobile as 手机,\r\nsocial_id as 证件号,\r\noper_date as 修改时间,\r\nmemo as '备注'\r\nfrom t_rm_vip_info ";

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds, "会员");
                            uiDataGridView2.DataSource = ds.Tables["会员"];
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询失败: " + ex.Message);
                }
            }
        }
        //商品查询
        private void item_Click(object sender, EventArgs e)
        {
            string connectionString = PublicMethod.Sql_Link_Str();          
            string query = "select  \r\nitem_no as 货号,\r\nitem_subno as 自编码,\r\nitem_name as 品名,rtrim(ltrim(unit_no)) as 单位,\r\nitem_size as 规格,\r\nitem_clsno as 类别,\r\nmain_supcust as 供应商,\r\n(select code_name from t_bd_base_code where type_no = 'pp' and code_id = t_bd_item_info.item_brand) as 品牌,\r\nproduct_area as 产地,\r\nmodify_date as 修改日期,\r\nprice  as 进价,sale_price as 零售价,\r\n\r\n(case item_stock when '0' then '是' when '1' then '否' end) as 库存管理,\r\nsup_ly_rate as 联营扣率,\r\n(case num3 when '0' then '普通' when '1' then '计重' when '2' then '计数' else '普通' end) as 计价方式,\r\ncombine_sta   as 商品类型,\r\nchange_price as 前台议价,\r\nbranch_price as 分店变价,\r\nen_dis as 前台打折,\r\nvip_acc_flag as 是否积分,\r\nstatus as 商品状态 \r\nfrom t_bd_item_info";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds, "商品");
                            uiDataGridView2.DataSource = ds.Tables["商品"];
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询失败: " + ex.Message);
                }
            }
        }
        //组合商品查询
        private void comb_item_Click(object sender, EventArgs e)
        {
            string connectionString = PublicMethod.Sql_Link_Str();
            // 查询语句
            string query = "SELECT \r\n\tcomb_item_no as 复合商品,\r\n\titem_no  as 成分编码,\r\n\titem_qty  as 成分数量\r\n  FROM t_sys_item_combsplit ";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds, "组合商品");
                            uiDataGridView2.DataSource = ds.Tables["组合商品"];
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询失败: " + ex.Message);
                }
            }
        }
        //附加条码查询
        private void item_barcode_Click(object sender, EventArgs e)
        {
            string connectionString = PublicMethod.Sql_Link_Str();
            // 查询语句
            string query = "SELECT item_no as '主货号',\r\n\titem_barcode as '附加条码'\r\n  FROM t_bd_item_barcode";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(query, conn))
                    {
                        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
                        {
                            DataSet ds = new DataSet();
                            adapter.Fill(ds, "附加条码");
                            uiDataGridView2.DataSource = ds.Tables["附加条码"];
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("查询失败: " + ex.Message);
                }
            }
        }
    }
}
